package schudel.daniel.androidspellingquiz;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DB extends SQLiteOpenHelper {
    final static int DB_VERSION                = 9;
    final static String DB_NAME                = "spelling.s3db";

    final static String TABLE_LISTS            = "lists";
    final static String TABLE_LISTS_ID         = "_id";
    final static String TABLE_LISTS_NAME       = "name";
    final static String TABLE_LISTS_CREATED    = "created";
    final static String TABLE_LISTS_ITERATIONS = "iterations";

    final static String TABLE_WORDS            = "words";
    final static String TABLE_WORDS_ID         = "_id";
    final static String TABLE_WORDS_NAME       = "word";
    final static String TABLE_WORDS_LIST_ID    = "table_list_id";

    Context context;
	     
    public DB(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        // Store the context for later use
        this.context = context;
    }
	
    @Override
    public void onCreate(SQLiteDatabase database) {
        Log.d("DB: ", "onCreate");
        Log.d("DB: ", "Create initial database");
        database.execSQL( "CREATE TABLE lists (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, created INTEGER NOT NULL, iterations INTEGER)");
        database.execSQL( "CREATE TABLE words (_id INTEGER PRIMARY KEY AUTOINCREMENT, word TEXT NOT NULL, table_list_id INTEGER NOT NULL)");
        addList(database, "Example List");
        addWordToList(database, "Example List", "red");
        addWordToList(database, "Example List", "yellow");
        addWordToList(database, "Example List", "green");
        addWordToList(database, "Example List", "blue");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {      
        if (newVersion != oldVersion) {
            Log.d("DB: ", "Versions differ");
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_LISTS);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_WORDS);
            onCreate(db);
        }
    }

    public void addList(String listName) {
        Log.d("DB: ", "Add list \"" + listName + "\"");
        SQLiteDatabase db = getWritableDatabase();
        addList(db, listName);
        db.close(); // Closing database connection
    }

    private void addList(SQLiteDatabase db, String listName) {
        Log.d("DB: ", "Add list to db \"" + listName + "\"");

        ContentValues values = new ContentValues();
        values.put(TABLE_LISTS_NAME,       listName);
        values.put(TABLE_LISTS_CREATED,    System.currentTimeMillis()/1000); // seconds since 1970
        values.put(TABLE_LISTS_ITERATIONS, 0);
        db.insert(TABLE_LISTS, null, values);
    }

    public void addWordToList(String listName, String word) {
        Log.d("DB: ", "Add word \"" + word + "\" to list \"" + listName + "\"");
        SQLiteDatabase db = getWritableDatabase();
        addWordToList(db, listName, word);
        db.close(); // Closing database connection
    }

    private void addWordToList(SQLiteDatabase db, String listName, String word) {
        Log.d("DB: ", "Add word \"" + word + "\" to list db \"" + listName + "\"");

        int id = listIdFromName(db, listName);

        ContentValues values = new ContentValues();
        values.put(TABLE_WORDS_NAME,       word);
        values.put(TABLE_WORDS_LIST_ID,    id);

        db.insert(TABLE_WORDS, null, values);
    }


    private int listIdFromName(String name) {
        SQLiteDatabase db = getReadableDatabase();
        int id = listIdFromName(db, name);
        db.close();
        return id;
    }

    private int listIdFromName(SQLiteDatabase db, String name) {
        String selectQuery = "SELECT " + TABLE_LISTS_ID + " FROM " + TABLE_LISTS + " WHERE " + TABLE_LISTS_NAME + "==\""  + name + "\"";
        Cursor cursor     = db.rawQuery(selectQuery, null);
        int id = 0; // TODO, do something if the list id is not found, though this should never happen
        if (cursor.moveToFirst()) {
            id = cursor.getInt(0);
        }
        return id;
    }

    public String getFirstListName() {
        SQLiteDatabase db = getReadableDatabase();
        String selectQuery = "SELECT " + TABLE_LISTS_NAME + " FROM " + TABLE_LISTS + " ORDER BY " + TABLE_LISTS_NAME + " ASC LIMIT 1";
        Cursor cursor     = db.rawQuery(selectQuery, null);
        String n = ""; // TODO, do something if no list is found, though this should never happen
        if (cursor.moveToFirst()) {
            n = cursor.getString(0);
        }
        db.close();
        return n;
    }

    public ArrayList<String> getWords(String listName) {
        ArrayList<String> list = new ArrayList<String>();

        int id = listIdFromName(listName);

        String selectQuery = "SELECT " + TABLE_WORDS_NAME + " FROM " + TABLE_WORDS + " WHERE " + TABLE_WORDS_LIST_ID + "=" + id + " ORDER by " + TABLE_WORDS_NAME + " ASC";
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor     = db.rawQuery(selectQuery, null);
     
        if (cursor.moveToFirst()) {
            do {
                list.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }
     
        db.close();
        
        return list;
    }

    public ArrayList<String> getLists() {
        ArrayList<String> list = new ArrayList<String>();

        String selectQuery = "SELECT * FROM " + TABLE_LISTS + " ORDER by " + TABLE_LISTS_NAME + " ASC";
     
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor     = db.rawQuery(selectQuery, null);
     
        if (cursor.moveToFirst()) {
            do {
                list.add(cursor.getString(1));
            } while (cursor.moveToNext());
        }
     
        db.close();
        
        return list;
    }

    public void deleteWordFromList(String listName, String word) {
        int id = listIdFromName(listName);

        Log.d("DB: ", "Delete Word " + word + " from " + listName + "(" + id + ")");
        SQLiteDatabase db = getWritableDatabase();
        
        String query = "DELETE FROM " + TABLE_WORDS + " WHERE " + TABLE_WORDS_LIST_ID + "=" + id + " AND " + TABLE_WORDS_NAME + "=\"" + word + "\"";
        db.execSQL(query);
        db.close();
    }

    public void deleteList(String listName) {
        int id = listIdFromName(listName);

        Log.d("DB: ", "Delete List " + listName + "(" + id + ")");
        SQLiteDatabase db = getWritableDatabase();

        String query = "DELETE FROM " + TABLE_WORDS + " WHERE " + TABLE_WORDS_LIST_ID + "=" + id;
        db.execSQL(query);

        query        = "DELETE FROM " + TABLE_LISTS + " WHERE " + TABLE_LISTS_ID + "=" + id;
        db.execSQL(query);
        db.close();
    }
}
